﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using DTS = Microsoft.SqlServer.Dts.Runtime;
using DTSRW = Microsoft.SqlServer.Dts.Runtime.Wrapper;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;

namespace BILAB.Technical.History.PackageBuilder
{
    public class SimpleExport
    {
        public SimpleExport(string name)
        {
            DTS.Package p = new Microsoft.SqlServer.Dts.Runtime.Package();
            p.Name = name;
            p.CreatorComputerName = System.Environment.MachineName;
            p.CreatorName = System.Environment.UserName;

            
            DTS.TaskHost t = (DTS.TaskHost)  p.Executables.Add("DTS.Pipeline");
            t.Name = "Main Data Flow";
            MainPipe dataFlow = (MainPipe) t.InnerObject;


            #region Add Source Connection 
            // Define a Connection Manager to database in connection referential
            DTS.ConnectionManager databaseCM = p.Connections.Add("OLEDB");
            databaseCM.Name = "AdventureDWSource";
            databaseCM.ConnectionString = @"Provider=SQLOLEDB.1;Password=myuser;Persist Security Info=True;User ID=myuser;Initial Catalog=AdventureWorksDW;Data Source=FREL20629";

            // Create a new OleDB source for the package
            IDTSComponentMetaData100 oledbDatasource = dataFlow.ComponentMetaDataCollection.New();
            oledbDatasource.ComponentClassID = "DTSAdapter.OleDbSource";          


            // Instanciate an OleDB source Instance for design time based on OleDB Source
            CManagedComponentWrapper oledbSourceInstance = oledbDatasource.Instantiate();
            oledbSourceInstance.ProvideComponentProperties();
            

            // Relink runtime oledb component with the package connection defined before
            oledbDatasource.RuntimeConnectionCollection[0].ConnectionManagerID = databaseCM.ID;
            oledbDatasource.RuntimeConnectionCollection[0].ConnectionManager 
                            = DTS.DtsConvert.GetExtendedInterface(databaseCM);

            // Sets properties
            oledbSourceInstance.SetComponentProperty("AccessMode",2);
            oledbSourceInstance.SetComponentProperty("SqlCommand", "Select * from FactInternetSales");

            // Populates metadata with active connections
            oledbSourceInstance.AcquireConnections(null);
            oledbSourceInstance.ReinitializeMetaData();
            oledbSourceInstance.ReleaseConnections();
            #endregion

            #region Add target
            // Creates flatFile ConnectionManager
            DTS.ConnectionManager flatFileCM = p.Connections.Add("FLATFILE");
            flatFileCM.ConnectionString = "test.txt";
            flatFileCM.Name = "destinationFile";
            flatFileCM.Properties["Format"].SetValue(flatFileCM, "delimited");
            flatFileCM.Properties["DataRowsToSkip"].SetValue(flatFileCM, 0);
            flatFileCM.Properties["ColumnNamesInFirstDataRow"].SetValue(flatFileCM, false);
            flatFileCM.Properties["RowDelimiter"].SetValue(flatFileCM, "\r\n");
            flatFileCM.Properties["TextQualifier"].SetValue(flatFileCM, "\"");           

            // Instanciate a FlatFile destination and link it to connection manager
            IDTSComponentMetaData100 flatfileDestination = dataFlow.ComponentMetaDataCollection.New();

            // Set up "model"
            flatfileDestination.ComponentClassID = "DTSAdapter.FlatFileDestination";
            flatfileDestination.Name = "Flat File Destination";
            flatfileDestination.Description = "Flat file destination";

            // Instanciate a destination "controller"
            CManagedComponentWrapper flatfileDestinationInstance = flatfileDestination.Instantiate();
            flatfileDestinationInstance.ProvideComponentProperties();

            // Link "Connection Manager" connection to the flat file destination model
            flatfileDestination.RuntimeConnectionCollection[0].ConnectionManagerID = flatFileCM.ID;
            flatfileDestination.RuntimeConnectionCollection[0].ConnectionManager = DTS.DtsConvert.GetExtendedInterface(flatFileCM);

            flatfileDestinationInstance.AcquireConnections(null);
            flatfileDestinationInstance.ReinitializeMetaData();
            flatfileDestinationInstance.ReleaseConnections();
            #endregion



            #region IV - links Source & Destination together
            IDTSOutput100 start = oledbDatasource.OutputCollection[0];
            IDTSInput100 end = flatfileDestination.InputCollection[0];

            IDTSPath100 path = dataFlow.PathCollection.New();
            path.AttachPathAndPropagateNotifications(start, end);
            path.Name = "SourceToDestination";
            #endregion

            #region Creates expected flat file columns
            DTSRW.IDTSConnectionManagerFlatFile100 flatfile = (DTSRW.IDTSConnectionManagerFlatFile100)flatFileCM.InnerObject;
            IDTSVirtualInputColumnCollection100 vColumns = flatfileDestination.InputCollection[0].GetVirtualInput().VirtualInputColumnCollection;
            for (int cols = 0; cols < vColumns.Count; cols++)
            {
                DTSRW.IDTSConnectionManagerFlatFileColumn100 col = flatfile.Columns.Add();
                col.DataType = vColumns[cols].DataType;
                col.DataType = vColumns[cols].DataType;
                col.DataPrecision = vColumns[cols].Precision;
                col.DataScale = vColumns[cols].Scale;
                DTSRW.IDTSName100 colname = (DTSRW.IDTSName100) col;
                colname.Name = vColumns[cols].Name;


            }

            #endregion

            DTS.Application a = new DTS.Application();
            a.SaveToXml(name + ".dtsx", p, null);
        }

    }
}
